<?php 
	include_once 'dataservice.php';
	include_once 'thuvienham.php';

	class hopdong_db 
	{
		public function Them($MaGiaoVien, $MaLopHoc, $MaVaiTro, $LuongMoiTiet, $TuNgay, $DenNgay, $NgayKy, $PIN)
		{
			$sql = "INSERT INTO `tbl_hopdong`(`MaGiaoVien`, `MaLopHoc`, `MaVaiTro`, `LuongMoiTiet`, `TuNgay`, `DenNgay`, `NgayKy`, `PIN`)
					VALUES ('$MaGiaoVien', '$MaLopHoc', '$MaVaiTro', '$LuongMoiTiet', '$TuNgay', '$DenNgay', '$NgayKy', '$PIN')";

			$service = new dataservice();
			return $service->ExecuteNonQuery($sql);
		}

		public function Xoa($MaHopDong)
		{
			$sql = "DELETE FROM `tbl_hopdong` WHERE `MaHopDong` = '$MaHopDong'";

			$service = new dataservice();
			return $service->ExecuteNonQuery($sql);
		}
		public function XoaGiaoVien($MaGiaoVien)
		{
			$sql = "DELETE FROM `tbl_hopdong` WHERE `MaGiaoVien` = '$MaGiaoVien'";

			$service = new dataservice();
			return $service->ExecuteNonQuery($sql);
		}
		public function XoaLopHoc($MaLopHoc)
		{
			$sql = "DELETE FROM `tbl_hopdong` WHERE `MaLopHoc` = '$MaLopHoc'";
			$service = new dataservice();
			return $service->ExecuteNonQuery($sql);
		}
		
		public function CapNhat($MaHopDong, $MaGiaoVien, $MaLopHoc, $MaVaiTro, $LuongMoiTiet, $TuNgay, $DenNgay, $NgayKy, $PIN)
		{
			$sql = "UPDATE `tbl_hopdong`
					SET `MaGiaoVien` = '$MaGiaoVien', `MaLopHoc` = '$MaLopHoc', `MaVaiTro` = '$MaVaiTro', `LuongMoiTiet` = '$LuongMoiTiet', `TuNgay` = '$TuNgay', `DenNgay` = '$DenNgay', `NgayKy` = '$NgayKy', `PIN` = '$PIN'
					WHERE `MaHopDong` = '$MaHopDong'";

			$service = new dataservice();
			return $service->ExecuteNonQuery($sql);
		}
		
		public function CapNhatPIN($MaHopDong, $PIN)
		{
			$sql = "UPDATE `tbl_hopdong`
					SET `PIN` = '$PIN'
					WHERE `MaHopDong` = '$MaHopDong'";

			$service = new dataservice();
			return $service->ExecuteNonQuery($sql);
		}

		public function TimHopDong($MaLopHoc, $HoTenGiaoVien)
		{
			if(trim($HoTenGiaoVien) != "")
				$sql = "SELECT 	HD.* 
						FROM 	`tbl_hopdong` HD, tbl_giaovien GV  
						WHERE  	GV.MaGiaoVien = HD.MaGiaoVien 
								AND (GV.HoTen LIKE '%$HoTenGiaoVien%' OR 
									 HD.MaLopHoc LIKE '%$MaLopHoc%') ";
			else
				$sql = "SELECT 	HD.* 
						FROM 	`tbl_hopdong` HD, tbl_giaovien GV  
						WHERE  	GV.MaGiaoVien = HD.MaGiaoVien 
								AND HD.MaLopHoc LIKE '%$MaLopHoc%' ";
			
			$sql .= " ORDER BY `MaHopDong` ASC, `NgayKy` DESC ";
			$service = new dataservice();
			return $service->ExecuteQuery($sql);
		}
		public function TimHopDongPaging($MaLopHoc, $HoTenGiaoVien, $Start, $Limit)
		{
			if(trim($HoTenGiaoVien) != "")
				$sql = "SELECT 	* 
						FROM 	`tbl_hopdong` HD, tbl_giaovien GV  
						WHERE  	GV.MaGiaoVien = HD.MaGiaoVien 
								AND (GV.HoTen LIKE '%$HoTenGiaoVien%' OR 
									 HD.MaLopHoc LIKE '%$MaLopHoc%') ";
			else
				$sql = "SELECT 	* 
						FROM 	`tbl_hopdong` HD, tbl_giaovien GV  
						WHERE  	GV.MaGiaoVien = HD.MaGiaoVien 
								AND HD.MaLopHoc LIKE '%$MaLopHoc%' ";
			$sql .= " ORDER BY `MaHopDong` ASC, `NgayKy` DESC LIMIT $Start, $Limit ";
			$service = new dataservice();
			return $service->ExecuteQuery($sql);
		}
		
		public function HDTrongKhoa($MaKhoaHoc)
		{
			$sql = "SELECT 	HD.* 
					FROM 	`tbl_hopdong` HD, tbl_lophoc LH  
					WHERE  	HD.MaLopHoc = LH.MaLopHoc AND
							LH.MaKhoaHoc = '$MaKhoaHoc' ";
			$sql .= " ORDER BY `MaHopDong` ASC, `NgayKy` DESC ";
			
			$service = new dataservice();
			return $service->ExecuteQuery($sql);
		}
		
		public function LayDanhSach($MaHopDong, $MaGiaoVien, $MaLopHoc, $MaVaiTro, $LuongMoiTiet, $TuNgay, $DenNgay, $NgayKy, $PIN)
		{
			$sql = "SELECT * FROM `tbl_hopdong` WHERE 1  ";
			if($MaHopDong != -1)
				$sql .= " AND `MaHopDong` = '$MaHopDong' ";

			if($MaGiaoVien != "")
				$sql .= " AND `MaGiaoVien` = '$MaGiaoVien' ";

			if($MaLopHoc != "")
				$sql .= " AND `MaLopHoc` LIKE '%$MaLopHoc%' ";

			if($MaVaiTro != "")
				$sql .= " AND `MaVaiTro` = '$MaVaiTro' ";

			if($LuongMoiTiet != -1)
				$sql .= " AND `LuongMoiTiet` = '$LuongMoiTiet' ";

			if($TuNgay != "0000-00-00")
				$sql .= " AND `TuNgay` = '$TuNgay' ";

			if($DenNgay != "0000-00-00")
				$sql .= " AND `DenNgay` = '$DenNgay' ";

			if($NgayKy != "0000-00-00")
				$sql .= " AND `NgayKy` = '$NgayKy' ";

			if($PIN != "")
				$sql .= " AND `PIN` = '$PIN' ";
			$sql .= " ORDER BY `MaHopDong` ASC, `NgayKy` DESC ";
			
			$service = new dataservice();
			return $service->ExecuteQuery($sql);
		}
		public function HopDongTheoKhoa($MaKhoaHoc, $MaHopDong, $MaGiaoVien, $MaLopHoc, $MaVaiTro, $LuongMoiTiet, $TuNgay, $DenNgay, $NgayKy, $PIN)
		{
			$sql = "SELECT HD.* 
					FROM  `tbl_hopdong` HD,  `tbl_lophoc` LH,  `tbl_khoahoc` KH
					WHERE LH.MaLopHoc = HD.MaLopHoc
					AND KH.MaKhoaHoc = LH.MaKhoaHoc
					AND KH.MaKhoaHoc =  '$MaKhoaHoc' ";
			if($MaHopDong != -1)
				$sql .= " AND `MaHopDong` = '$MaHopDong' ";

			if($MaGiaoVien != "")
				$sql .= " AND `MaGiaoVien` = '$MaGiaoVien' ";

			if($MaLopHoc != "")
				$sql .= " AND `MaLopHoc` LIKE '%$MaLopHoc%' ";

			if($MaVaiTro != "")
				$sql .= " AND `MaVaiTro` = '$MaVaiTro' ";

			if($LuongMoiTiet != -1)
				$sql .= " AND `LuongMoiTiet` = '$LuongMoiTiet' ";

			if($TuNgay != "0000-00-00")
				$sql .= " AND `TuNgay` = '$TuNgay' ";

			if($DenNgay != "0000-00-00")
				$sql .= " AND `DenNgay` = '$DenNgay' ";

			if($NgayKy != "0000-00-00")
				$sql .= " AND `NgayKy` = '$NgayKy' ";

			if($PIN != "")
				$sql .= " AND `PIN` = '$PIN' ";
			$sql .= " ORDER BY `MaHopDong` ASC, `NgayKy` DESC ";
			
			$service = new dataservice();
			return $service->ExecuteQuery($sql);
		}
		public function HopDongTheoKhoaPaging($MaKhoaHoc, $MaHopDong, $MaGiaoVien, $MaLopHoc, $MaVaiTro, $LuongMoiTiet, $TuNgay, $DenNgay, $NgayKy, $PIN, $Start, $Limit)
		{
			$sql = "SELECT HD.* 
					FROM  `tbl_hopdong` HD,  `tbl_lophoc` LH,  `tbl_khoahoc` KH
					WHERE LH.MaLopHoc = HD.MaLopHoc
					AND KH.MaKhoaHoc = LH.MaKhoaHoc
					AND KH.MaKhoaHoc =  '$MaKhoaHoc' ";
			if($MaHopDong != -1)
				$sql .= " AND `MaHopDong` = '$MaHopDong' ";

			if($MaGiaoVien != "")
				$sql .= " AND `MaGiaoVien` = '$MaGiaoVien' ";

			if($MaLopHoc != "")
				$sql .= " AND `MaLopHoc` LIKE '%$MaLopHoc%' ";

			if($MaVaiTro != "")
				$sql .= " AND `MaVaiTro` = '$MaVaiTro' ";

			if($LuongMoiTiet != -1)
				$sql .= " AND `LuongMoiTiet` = '$LuongMoiTiet' ";

			if($TuNgay != "0000-00-00")
				$sql .= " AND `TuNgay` = '$TuNgay' ";

			if($DenNgay != "0000-00-00")
				$sql .= " AND `DenNgay` = '$DenNgay' ";

			if($NgayKy != "0000-00-00")
				$sql .= " AND `NgayKy` = '$NgayKy' ";

			if($PIN != "")
				$sql .= " AND `PIN` = '$PIN' ";
			$sql .= " ORDER BY `MaHopDong` ASC, `NgayKy` DESC LIMIT $Start, $Limit ";
			
			$service = new dataservice();
			return $service->ExecuteQuery($sql);
		}
		
		public function LayDanhSachPaging($MaHopDong, $MaGiaoVien, $MaLopHoc, $MaVaiTro, $LuongMoiTiet, $TuNgay, $DenNgay, $NgayKy, $PIN, $Start, $Limit)
		{
			$sql = "SELECT * FROM `tbl_hopdong` WHERE 1  ";
			if($MaHopDong != -1)
				$sql .= " AND `MaHopDong` = '$MaHopDong' ";

			if($MaGiaoVien != "")
				$sql .= " AND `MaGiaoVien` = '$MaGiaoVien' ";

			if($MaLopHoc != "")
				$sql .= " AND `MaLopHoc` LIKE '%$MaLopHoc%' ";

			if($MaVaiTro != "")
				$sql .= " AND `MaVaiTro` = '$MaVaiTro' ";

			if($LuongMoiTiet != -1)
				$sql .= " AND `LuongMoiTiet` = '$LuongMoiTiet' ";

			if($TuNgay != "0000-00-00")
				$sql .= " AND `TuNgay` = '$TuNgay' ";

			if($DenNgay != "0000-00-00")
				$sql .= " AND `DenNgay` = '$DenNgay' ";

			if($NgayKy != "0000-00-00")
				$sql .= " AND `NgayKy` = '$NgayKy' ";

			if($PIN != "")
				$sql .= " AND `PIN` = '$PIN' ";
			$sql .= " ORDER BY `MaHopDong` ASC, `NgayKy` DESC LIMIT $Start, $Limit ";
			$service = new dataservice();
			return $service->ExecuteQuery($sql);
		}
		
	}

